﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace fanfu.Common
{   
    /// <summary>
    /// SQLServer数据库操作类
    /// </summary>
    public class SqlHelper
    {
        private static SqlConnection connection;

        /// <summary>
        /// SqlConnection
        /// </summary>
        private static SqlConnection Connection
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["SqlConnString"].ConnectionString;
                if (connection == null)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection = new SqlConnection(connectionString);
                    connection.Open();
                }
                else if (connection.State == System.Data.ConnectionState.Broken)
                {
                    connection.Close();
                    connection.Open();
                }
                return connection;
            }
        }

        #region 执行增删改ExecuteNonQuery
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string safeSql)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
                {
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                return -1;
            }
        }
        
        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, SqlParameter value)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, Connection))
                {
                    cmd.Parameters.Add(value);
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                return -1;
            }
        }

        /// <summary>
        /// 执行增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static int ExecuteCommand(string sql, params SqlParameter[] values)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, Connection))
                {
                    cmd.Parameters.AddRange(values);
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (SqlException ex)
            {
                return -1;
            }
        }
        #endregion

        #region 查询单个值ExecuteScalar
        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static object GetScalar(string safeSql)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
                {
                    return cmd.ExecuteScalar();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object GetScalar(string sql, SqlParameter value)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, Connection))
                {
                    cmd.Parameters.Add(value);
                    return cmd.ExecuteScalar();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 查询单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static object GetScalar(string sql, params SqlParameter[] values)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, Connection))
                {
                    cmd.Parameters.AddRange(values);
                    return cmd.ExecuteScalar();
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        #endregion

        #region 返回DataReader 
        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string safeSql)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
                {
                    SqlDataReader reader = cmd.ExecuteReader();
                    return reader;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="safeSql"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string safeSql, SqlParameter value)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(safeSql, Connection))
                {
                    cmd.Parameters.Add(value);
                    SqlDataReader reader = cmd.ExecuteReader();
                    return reader;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }
        
        /// <summary>
        /// 返回DataReader
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
        {
            try
            {
                using (SqlCommand cmd = new SqlCommand(sql, Connection))
                {
                    cmd.Parameters.AddRange(values);
                    SqlDataReader reader = cmd.ExecuteReader();
                    return reader;
                }
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        #endregion

        #region 返回dataTable
        /// <summary>
        /// 返回dataTable
        /// </summary>
        /// <param name="safeSql"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string safeSql)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(safeSql, Connection);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        /// <summary>
        /// 返回dataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, SqlParameter value)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.Add(value);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }

        /// <summary>
        /// 返回dataTable
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="values"></param>
        /// <returns></returns>
        public static DataTable GetDataTable(string sql, params SqlParameter[] values)
        {
            DataSet ds = new DataSet();
            SqlCommand cmd = new SqlCommand(sql, Connection);
            cmd.Parameters.AddRange(values);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(ds);
            return ds.Tables[0];
        }
        #endregion
    }
}